using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using Microsoft.ConnectionEngine.Registers.Core.ServiceProvider.Common;
using Microsoft.ConnectionEngine.Registers.Core.ServiceProvider.Common.DataSets;

using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Configuration;

namespace Microsoft.ConnectionEngine.Registers.Core.ServiceProvider.DataAccess
{
    public class Pool
    {

        #region Get/search methods

        /// <summary>
        /// Gets a specific Pool by ID
        /// </summary>
        /// <param name="PoolID">The id of the pool to retrieve</param>
        /// <returns>A typed dataset representing the requested pool</returns>
        [CLSCompliant(false)]
        public static dsPool PoolSelectItem(string poolID)
        {
            dsPool ds = new dsPool();
            string SQL = "PoolSelectByID";

            Database db = DatabaseFactory.CreateDatabase(DataAccessHelper.GetDefaultConnectionStringName());
            ds.Pool.Load(db.ExecuteReader(SQL,poolID));
            return ds;
        }

        /// <summary>
        /// Returns true if a Pool row exists
        /// </summary>
        /// <returns>True if successful</returns>
        public static bool PoolExists(string PoolDescription)
        {
            bool result = false;
            string SQL = "PoolExists";

            Database db = DatabaseFactory.CreateDatabase(DataAccessHelper.GetDefaultConnectionStringName());
            if (Convert.ToInt32(db.ExecuteScalar(SQL, PoolDescription)) > 0)
                result = true;

            return result;
        }

      


        /// <summary>
        /// Gets all pools
        /// </summary>
        /// <returns>A typed dataset representing all pools</returns>
        [CLSCompliant(false)]
        public static dsPool PoolSearch(string poolName, string serviceProviderTypeId)
        {
        //return a list of pools by name or by spt
            dsPool ds = new dsPool();
            string SQL = "PoolSearch";

            Database db = DatabaseFactory.CreateDatabase(DataAccessHelper.GetDefaultConnectionStringName());
            if (String.IsNullOrEmpty(serviceProviderTypeId))
                ds.Pool.Load(db.ExecuteReader(SQL, poolName, System.DBNull.Value));
            else
                ds.Pool.Load(db.ExecuteReader(SQL, poolName, int.Parse(serviceProviderTypeId)));
            return ds;
        }

        /// <summary>
        /// Returns a list of pools based on search criteria
        /// </summary>
        /// <param name="displayname">Name search field</param>
        /// <param name="serviceprovidertypeid">Service Provider type search field</param>
        /// <param name="poolid">Pool search ID</param>
        /// <returns></returns>
        [CLSCompliant(false)]
        public static dsPool PoolSearch(string displayname, string serviceprovidertypeid, string serviceproviderid, bool inserviceprovider)
        {
            dsPool ds = new dsPool();
            string sp;

            //Work Item ID: 132
            //Modified By: SachinJo
            //Modified to string.length instead of empty string("") comparision.
            if (serviceprovidertypeid.Trim().Length == 0) serviceprovidertypeid = null;
            if (serviceproviderid.Trim().Length == 0) serviceproviderid = null;

            if (inserviceprovider)
            {
                sp = "PoolSearch";
            }
            else
            {
                sp = "PoolSearchNotIn";
            }
            Database db = DatabaseFactory.CreateDatabase(DataAccessHelper.GetDefaultConnectionStringName());
            ds.Pool.Load(db.ExecuteReader(sp, displayname, serviceprovidertypeid, serviceproviderid), LoadOption.OverwriteChanges);

            return ds;
        }


        #endregion

        #region Check in/out methods

        /// <summary>
        /// Locks a pool
        /// </summary>
        /// <param name="poolID">The pool to be locked</param>
        /// <param name="userID">The user that is locking the pool</param>
        /// <returns>True if successfull)</returns>
        public static bool PoolLock(string poolID, string userID)
        {
            bool result = true;
            string SQL = "PoolLock";

            Database db = DatabaseFactory.CreateDatabase(DataAccessHelper.GetDefaultConnectionStringName());
            if (db.ExecuteNonQuery(SQL,poolID,userID) <= 0)
                result = false;

            return result;
        }

        /// <summary>
        /// Unlocks a pool
        /// </summary>
        /// <param name="poolID">The pool to be unlocked</param>
        /// <returns>True if successfull)</returns>
        public static bool PoolUnlock(string poolID)
        {
            bool result = true;
            string SQL = "PoolUnlock";

            Database db = DatabaseFactory.CreateDatabase(DataAccessHelper.GetDefaultConnectionStringName());
            if (db.ExecuteNonQuery(SQL,poolID) <= 0)
                result = false;

            return result;
        }


        /// <summary>
        /// Unlocks all pools locked by the user
        /// </summary>
        /// <param name="userID">The user that is locking all their pools</param>
        /// <returns>True if successfull)</returns>
        public static bool PoolUnlockAll(string userID)
        {
            bool result = true;
            string SQL = "PoolUnlockAll";

            Database db = DatabaseFactory.CreateDatabase(DataAccessHelper.GetDefaultConnectionStringName());
            db.ExecuteNonQuery(SQL,userID);

            return result;
        }


        /// <summary>
        /// Tests if a pool is locked
        /// </summary>
        /// <param name="poolid">The pool to be tested</param>
        /// <returns>True if checked out</returns>
        public static bool PoolIsLocked(string poolID)
        {
            bool result = false;
            string SQL = "PoolIsLocked";

            Database db = DatabaseFactory.CreateDatabase(DataAccessHelper.GetDefaultConnectionStringName());
            if (Convert.ToInt32(db.ExecuteScalar(SQL,poolID)) != 0)
                result = true;

            return result;
        }


        /// <summary>
        /// Tests if a pool is locked by a user
        /// </summary>
        /// <param name="poolid">The pool that is to to be tested</param>
        /// <param name="userID">The user that may have the object locked</param>
        /// <returns>True if successful</returns>
        public static bool PoolIsLockedByUser(string poolID, string userID)
        {
            bool result = false;
            string SQL = "PoolIsLockedByUser";


           Database db = DatabaseFactory.CreateDatabase(DataAccessHelper.GetDefaultConnectionStringName());
            if (Convert.ToInt32(db.ExecuteScalar(SQL,poolID, userID)) != 0)
                result = true;

            return result;
        }

        #endregion

        #region Delete methods

        /// <summary>
        /// Deletes a pool
        /// </summary>
        /// <param name="poolid">The pool to delete</param>
        /// <returns>True if successful</returns>
        public static bool PoolDelete(string poolID)
        {
            bool result = true;
            string SQL = "PoolDelete";

           Database db = DatabaseFactory.CreateDatabase(DataAccessHelper.GetDefaultConnectionStringName());
            if (db.ExecuteNonQuery(SQL,poolID) <= 0)
                result = false;

            return result;
        }


        /// <summary>
        /// Deletes all ServiceProviderPool records belonging to a specific Pool
        /// </summary>
        /// <param name="poolid">The ID of the parent Pool</param>
        /// <returns>True if successful</returns>
        public static bool ServiceProviderPoolDeleteByPoolID(string poolID)
        {
            bool result = true;
            string SQL = "ServiceProviderPoolDeleteByPoolID";

           Database db = DatabaseFactory.CreateDatabase(DataAccessHelper.GetDefaultConnectionStringName());
           db.ExecuteScalar(SQL, poolID);                

            return result;
        }

        #endregion

        #region  Update/Insert methods

        /// <summary>
        /// Inserts a new Pool
        /// </summary>
        /// <param name="poolname"></param>
        /// <returns>A new ID if successful, otherwise empty string</returns>
        public static string PoolInsert(string poolName)
        {
            string result = string.Empty;
            string SQL = "PoolInsert";

            Database db = DatabaseFactory.CreateDatabase(DataAccessHelper.GetDefaultConnectionStringName());
            result  = db.ExecuteScalar(SQL,poolName).ToString();

            return result;
        }

        /// <summary>
        /// Updates an existing Pool
        /// </summary>
        /// <param name="poolid"></param>
        /// <param name="poolname"></param>
        /// <returns>True if successful</returns>
        public static bool PoolUpdate
        (
            string poolID,
            string poolName
        )
        {
            bool result = true;
            string SQL = "PoolUpdate";

            Database db = DatabaseFactory.CreateDatabase(DataAccessHelper.GetDefaultConnectionStringName());
            if (db.ExecuteNonQuery(SQL, poolID, poolName) <= 0)
                result = false;

            return result;
        }

        #endregion
    }
}
